package com.springboot.shanhexin.dao;

import com.springboot.shanhexin.entity.hotel;
import com.springboot.shanhexin.entity.place;
import com.springboot.shanhexin.entity.restaurant;
import com.springboot.shanhexin.entity.tourist;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Component;

import java.util.List;

@Component(value = "qiantaiDao")
public interface qiantaiDao {

    //获取景区内所有景点经纬度及id
    @Select("SELECT * FROM place a,scenic_spot b WHERE a.ssid=b.id and b.name like" +
            " concat(concat('%',#{name}),'%')")
    List<place> getallplace(@Param("name")String name);

    //获取某个景点的所有信息
    @Select("SELECT * FROM place where id=#{id}")
    List<place> getplaceone(@Param("id")int id);

    //获取某个景点附近的餐厅
    @Select("SELECT * FROM restaurant where pid=#{pid}")
    List<restaurant> getresone(@Param("pid")int pid);

    //获取某个景点附近的旅店
    @Select("SELECT * FROM hotel where pid=#{pid}")
    List<hotel> getholone(@Param("pid")int pid);

    //查找是否存在该用户
    @Select("select count(*) from userinfo where openid=#{openid}")
    int getuserCount(@Param("openid")String openid);

    //添加用户
    @Insert("INSERT INTO userinfo(openid) values(#{openid})")
    void adduseropenid(@Param("openid")String openid);

    //更新用户name
    @Update("update userinfo set name=#{name} where openid=#{openid}")
    void addusername(@Param("name")String name,@Param("openid")String openid);

    //获取路线tid
    @Select("select id from tourist_route where uid=#{uid} and title=#{title} and my_rode=#{my_rode}")
    int searchtid(@Param("uid")String uid,@Param("title")String title,@Param("my_rode")String my_rode);

    //查找是否存在该路线
    @Select("select count(*) from tourist_route where uid=#{uid} and title=#{uid} and my_rode=#{my_rode}")
    int gettouristCount(@Param("uid")String uid,@Param("title")String title,@Param("my_rode")String my_rode);

    //获取openid对应的所有我的路线
    @Select("SELECT * FROM tourist_route WHERE uid=#{uid} GROUP BY title")
    List<tourist> getalltourist(@Param("uid")String uid);

    //获取openid对应的一个我的路线
    @Select("SELECT * FROM tourist_route WHERE uid=#{uid} and title=#{title}")
    List<tourist> getonetourist(@Param("uid")String uid,@Param("title")String title);

    //添加我的路线（不用的表）
    @Insert("insert into tourist_route(uid,title,my_rode) values(#{uid},#{title},#{my_rode})")
    void addtourist(@Param("uid")String uid,@Param("title")String title,@Param("my_rode")String my_rode);

    //查找是否存在该餐厅
    @Select("select count(*) from my_restaurant where tid=#{tid} and name=#{name} and rid=#{rid}")
    int getmy_resCount(@Param("tid")int tid,@Param("name")String name,@Param("rid")int rid);

    //添加我的餐厅
    @Insert("INSERT INTO my_restaurant(tid,name,rid) values(#{tid},#{name},#{rid})")
    void addmy_res(@Param("tid")int tid,@Param("name")String name,@Param("rid")int rid);

    //查找是否存在该旅店
    @Select("select count(*) from my_hotel where tid=#{tid} and name=#{name} and hid=#{hid}")
    int getmy_holCount(@Param("tid")int tid,@Param("name")String name,@Param("hid")int hid);

    //添加我的旅店
    @Insert("INSERT INTO my_hotel(tid,name,hid) values(#{tid},#{name},#{hid})")
    void addmy_hol(@Param("tid")int tid,@Param("name")String name,@Param("hid")int hid);

    //路线对应的我的旅店
    @Select("select c.* from my_hotel a,tourist_route b,hotel c where a.tid=b.id " +
            "and a.hid=c.id and a.tid=#{tid}")
    List<hotel> getmy_hol(@Param("tid")int tid);

    //路线对应的我的餐厅
    @Select("select c.* from my_restaurant a,tourist_route b,restaurant c where a.tid=b.id " +
            "and a.rid=c.id and a.tid=#{tid}")
    List<restaurant> getmy_res(@Param("tid")int tid);

    //路线对应的我的旅店（微信）
    @Select("select c.* from my_hotel a,tourist_route b,hotel c " +
            "where a.tid=b.id and a.hid=c.id and b.uid=#{uid} " +
            "and b.my_rode=#{my_rode}")
    List<hotel> getmy_hol_wx(@Param("uid")String uid,@Param("my_rode")String my_rode);

    //路线对应的我的餐厅（微信）
    @Select("select c.* from my_restaurant a,tourist_route b,restaurant c " +
            "where a.tid=b.id and a.rid=c.id and b.uid=#{uid} " +
            "and b.my_rode=#{my_rode}")
    List<restaurant> getmy_res_wx(@Param("uid")String uid,@Param("my_rode")String my_rode);

    //用户路线对应的景点数
    @Select("SELECT count(*) FROM tourist_route WHERE uid=#{uid} and title=#{title}")
    int gettouristSpotCount(@Param("uid")String uid,@Param("title")String title);

    //用户路线对应的景点名称
    @Select("SELECT my_rode FROM tourist_route WHERE uid=#{uid} and title=#{title}")
    String [] gettouristSpotName(@Param("uid")String uid,@Param("title")String title);

    //用户路线所选景点对应的经度
    @Select("SELECT longitude FROM place WHERE name=#{spotname}")
    String gettouristSpotLong(@Param("spotname")String spotname);

    //用户路线所选景点对应的纬度度
    @Select("SELECT latitude FROM place WHERE name=#{spotname}")
    String gettouristSpotLat(@Param("spotname")String spotname);

    //根据景点名称找到id
    @Select("SELECT id FROM place WHERE name=#{spotname}")
    String getspotid(@Param("spotname")String spotname);

    //添加新的用户路线(最优路线和时间)
    @Insert("insert into my_tourist(uid,title,tourist,walk_time) values(#{uid},#{title},#{tourist},#{walk_time})")
    int addnewtourist(@Param("uid")String uid,@Param("title")String title,@Param("tourist")String tourist,
                       @Param("walk_time")String walk_time);
    //查找是否存在用户路线(最优路线和时间)
     @Select("SELECT count(*) FROM my_tourist WHERE uid=#{uid} and title=#{title}")
     int getnew_tourist(@Param("uid")String uid,@Param("title")String title);

     //删除路线(my_tourist)
    @Delete("DELETE FROM my_tourist WHERE uid=#{uid} and title=#{title}")
    void getDelete_tourist(@Param("uid")String uid,@Param("title")String title);

    //拿到要删除的tid
    @Select("select id from tourist_route where uid=#{uid} and title=#{title}")
    String [] get_tid(@Param("uid")String uid,@Param("title")String title);

    //删除路线（tourist_route）
    @Delete("DELETE FROM tourist_route WHERE uid=#{uid} and title=#{title}")
    void getDelete_tourist_route(@Param("uid")String uid,@Param("title")String title);

    //删除餐厅（my_res）
    @Delete("DELETE FROM my_restaurant WHERE tid=#{tid}")
    void getDelete_my_res(@Param("tid")String tid);

    //删除餐厅（my_hol）
    @Delete("DELETE FROM my_hotel WHERE tid=#{tid}")
    void getDelete_my_hol(@Param("tid")String tid);
}
